﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ARR.Entities;
using ARR.DataModel.Repositories;
using DDF.Entity;
using System.Transactions;
using OfficeOpenXml;
using System.Drawing;
using ARR.Services.Domain.Reports;
using OfficeOpenXml.Style;
using ARR.Model.Repositories;
using ARR.Entities.Gateways;

namespace ARR.Services.Domain
{
    public static class SpacesDomain
    {
        private static Color Red = Color.FromArgb(255, 0, 0);
        private static Color Green = Color.FromArgb(0, 155, 70);
        private static Color Yellow = Color.FromArgb(255, 255, 70);
        private static Color GrayLine = Color.FromArgb(217, 217, 217);
        private static Color BlueLine = Color.FromArgb(167, 191, 222);
        private static Color BlueText = Color.FromArgb(31, 73, 125);
        private static Color BlueTotalTableHeader = Color.FromArgb(79, 129, 189);
        private static Color CategoryComparisonYellow = Color.FromArgb(255, 255, 153);
        private static Color CategoryComparisonGray = Color.FromArgb(216, 216, 216);

        private static string FormatCurrencyTwoDecimals = "_ $ * #.##0,00_ ;_ $ * -#.##0,00_ ;_ $ * \" - \"??_ ;_ @_ ";
        private static string FormatThousandsOneDecimal = "_(k$* 0.##_);_(k$* (0.##);_(k$* \" - \"??_);_(@_)";
        private static string FormatThousandsTwoDecimals = "_(k$* 0.#_);_(k$* (0.#);_(k$* \" - \"??_);_(@_)";
        private static string FormatPercentageOneDecimal = @"0.0%";
        private static string FormatMetersTwoDecimal = @"0.00 ""mt.""";
        private static string FormatTwoDecimals = @"0.00";


        public static List<Space> GetAllSpaces(int rangeReviewId, bool lazyLoad)
        {
            var spaces = SpacesRepository.Select(x => x.RangeReviewId == rangeReviewId).ConvertAll<Space>(row => new Space(row));

            if (!lazyLoad)
            {
                //TODO: Cargar propiedades.
            }

            spaces.ForEach(st => st.ChangeState(EntityState.Original));

            return spaces;
        }

        public static Space GetSpaceById(int spaceId)
        {
            var row = SpacesRepository.Select(st => st.Id == spaceId).SingleOrDefault();
            var space = new Space(row);

            space.ChangeState(EntityState.Original);

            return space;
        }

        public static void SaveSpaces(List<Space> spaces)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                foreach (Space space in spaces)
                {
                    if (space.GetEntityState() == EntityState.New)
                    {
                        SpacesRepository.Insert(space.GetRow());
                    }
                    else if (space.GetEntityState() == EntityState.Modified)
                    {
                        SpacesRepository.Update(space.GetRow());
                    }
                }

                scope.Complete();
            }
        }

        public static List<TempSpace> GetAllTempSpaces(bool lazyLoad)
        {
            var spaces = TempSpacesRepository.Select().ConvertAll<TempSpace>(row => new TempSpace(row));

            if (!lazyLoad)
            {
                //TODO: Cargar propiedades.
            }

            spaces.ForEach(st => st.ChangeState(EntityState.Original));

            return spaces;
        }

        public static List<TempSpace> GetTempSpacesByIds(IEnumerable<int> listIds)
        {
            var rows = TempSpacesRepository.SelectByIdList(listIds.ToArray());

            List<TempSpace> sales = new List<TempSpace>();

            rows.ForEach(row => sales.Add(new TempSpace(row)));

            sales.ForEach(s => s.ChangeState(EntityState.Original));

            return sales;
        }

        public static void SaveTempSpaces(List<TempSpace> spaces)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                var toSave = spaces.Where(x => x.GetEntityState() != EntityState.Original);

                foreach (TempSpace space in toSave)
                {
                    if (space.GetEntityState() == EntityState.New)
                    {
                        TempSpacesRepository.Insert(space.GetRow());
                    }
                    else if (space.GetEntityState() == EntityState.Modified)
                    {
                        TempSpacesRepository.Update(space.GetRow());
                    }
                }

                scope.Complete();
            }
        }

        public static List<Planogram> GetAllPlanograms(int rangeReviewId, bool lazyLoad)
        {
            var planograms = PlanogramsRepository.Select(x => x.RangeReviewId == rangeReviewId).ConvertAll<Planogram>(row => new Planogram(row));

            if (!lazyLoad)
            {
                RangeReview range = RangeReviewDomain.GetById(rangeReviewId, true);
                var stores = StoresDomain.GetByCountry(range.CountryId, true);

                foreach (Planogram p in planograms)
                {
                    p.Store = stores.SingleOrDefault(x => x.Id == p.StoreId);
                    if (p.TypeId.HasValue)
                    {
                        p.PlanogramType = (PlanogramType)Enum.ToObject(typeof(PlanogramType), p.TypeId.Value);
                    }
                    else
                    {
                        p.PlanogramType = null;
                    }
                    p.ListingPlanogram = p.ListingPlanogramId.HasValue ? GetPlanogramById(p.ListingPlanogramId.Value) : null;

                    if (p.PlanogramLocationId.HasValue)
                    {
                        p.PlanogramLocation = GetPlanogramLocationById(p.PlanogramLocationId.Value);
                    }
                }
            }

            planograms.ForEach(st => st.ChangeState(EntityState.Original));

            return planograms;
        }

        public static List<PlanogramLocation> GetAllPlanogramLocations()
        {
            return PlanogramLocationsRepository.Select().ConvertAll<PlanogramLocation>(x => new PlanogramLocation(x));
        }

        private static PlanogramLocation GetPlanogramLocationById(int planogramLocationId)
        {
            var row = PlanogramLocationsRepository.Select(x => x.Id == planogramLocationId).SingleOrDefault();

            if (row != null)
                return new PlanogramLocation(row);

            return null;
        }

        public static Planogram GetPlanogramById(int planogramId)
        {
            var row = PlanogramsRepository.Select(x => x.Id == planogramId).SingleOrDefault();
            var planogram = new Planogram(row);

            planogram.ChangeState(EntityState.Original);

            return planogram;
        }

        public static void SavePlanograms(List<Planogram> planograms)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                foreach (Planogram planogram in planograms)
                {
                    if (planogram.GetEntityState() == EntityState.New)
                    {
                        PlanogramsRepository.Insert(planogram.GetRow());
                    }
                    else if (planogram.GetEntityState() == EntityState.Modified)
                    {
                        PlanogramsRepository.Update(planogram.GetRow());
                    }
                }

                scope.Complete();
            }
        }

        public static byte[] GenerateFloorPlan(int rangeReviewId)
        {
            RangeReview rangeReview = RangeReviewDomain.GetById(rangeReviewId, true);

            List<SpaceBaseFloorplan> spaceRows = SpacesRepository.GetFloorPlanReport(rangeReviewId);

            byte[] excelBytes = null;


            using (ExcelPackage p = new ExcelPackage())
            {
                p.Workbook.Properties.Author = "Tryada";
                p.Workbook.Properties.Title = "ARR";

                if (spaceRows.Any())
                {
                    var planograms = SpacesDomain.GetAllPlanograms(rangeReview.Id, true);

                    var linearTotals = new FloorplanData
                    {
                        Sales = spaceRows.Where(x => x.LinearMeters).Sum(x => x.Sales),
                        GM = spaceRows.Where(x => x.LinearMeters).Sum(x => x.GM),
                        SKUs = spaceRows.Where(x => x.LinearMeters).Distinct().Count(),
                        InventoryCost = spaceRows.Where(x => x.LinearMeters).Sum(x => x.InventoryCost),
                        Space = spaceRows.Where(x => x.LinearMeters).Sum(x => x.Width) / 100,
                    };

                    var squareTotals = new FloorplanData
                    {
                        Sales = spaceRows.Where(x => x.SquareMeters).Sum(x => x.Sales),
                        GM = spaceRows.Where(x => x.SquareMeters).Sum(x => x.GM),
                        SKUs = spaceRows.Where(x => x.SquareMeters).Distinct().Count(),
                        InventoryCost = spaceRows.Where(x => x.SquareMeters).Sum(x => x.InventoryCost),
                        Space = planograms.Where(x => x.SquareMeters.HasValue).Sum(x => x.SquareMeters.Value)
                    };

                    #region Level 1

                    p.Workbook.Worksheets.Add("Level 1");
                    ExcelWorksheet ws = p.Workbook.Worksheets[1];
                    ws.View.ShowGridLines = false;
                    ws.View.ZoomScale = 80;
                    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
                    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet


                    var linearDataLevel1 = from item in spaceRows.Where(x => x.LinearMeters)
                                           group item by item.Level1 into g
                                           select new FloorplanData
                                           {
                                               Level = g.Key,
                                               Sales = g.Sum(x => x.Sales),
                                               GM = g.Sum(x => x.GM),
                                               SKUs = g.Count(),
                                               InventoryCost = g.Sum(x => x.InventoryCost),
                                               Space = g.Sum(x => x.Width) / 100,
                                               SalesPerc = linearTotals.Sales != 0 ? g.Sum(x => x.Sales) / linearTotals.Sales : 0,
                                               GMPerc = linearTotals.GM != 0 ? g.Sum(x => x.GM) / linearTotals.GM : 0,
                                               SKUsPerc = linearTotals.SKUs != 0 ? g.Count() / linearTotals.SKUs : 0,
                                               InventoryCostPerc = linearTotals.InventoryCost != 0 ? g.Sum(x => x.InventoryCost) / linearTotals.InventoryCost : 0,
                                               SpacePerc = linearTotals.Space != 0 ? (g.Sum(x => x.Width) / 100) / linearTotals.Space : 0,
                                           };

                    var squareDataLevel1 = from item in spaceRows.Where(x => x.SquareMeters)
                                           group item by item.Level1 into g
                                           select new FloorplanData
                                           {
                                               Level = g.Key,
                                               Sales = g.Sum(x => x.Sales),
                                               GM = g.Sum(x => x.GM),
                                               SKUs = g.Count(),
                                               InventoryCost = g.Sum(x => x.InventoryCost),
                                               Space = planograms.Where(x => x.Level1 == g.Key && x.SquareMeters.HasValue).Any() ? planograms.Where(x => x.Level1 == g.Key && x.SquareMeters.HasValue).First().SquareMeters.Value : 0,
                                               SalesPerc = squareTotals.Sales != 0 ? g.Sum(x => x.Sales) / squareTotals.Sales : 0,
                                               GMPerc = squareTotals.GM != 0 ? g.Sum(x => x.GM) / squareTotals.GM : 0,
                                               SKUsPerc = squareTotals.SKUs != 0 ? g.Count() / squareTotals.SKUs : 0,
                                               InventoryCostPerc = squareTotals.InventoryCost != 0 ? g.Sum(x => x.InventoryCost) / squareTotals.InventoryCost : 0,
                                               SpacePerc = squareTotals.Space != 0 ? planograms.Where(x => x.Level1 == g.Key && x.SquareMeters.HasValue).Sum(x => x.SquareMeters.Value) / squareTotals.Space : 0
                                           };


                    ExcelAddressNumbers totalStoreAddress = ExcelHelper.SplitAddress("B2");
                    CreateTotalStoreTable(totalStoreAddress, ws, linearTotals, squareTotals);

                    CreateSemaphoreTable("F2", ws, rangeReview);

                    int levels = linearDataLevel1.Select(x => x.Level).Distinct().Count();
                    ExcelAddressNumbers titleAddress = ExcelHelper.SplitAddress("B10");

                    CreateTitleLine(titleAddress, ws, "Level 1: Square Meters", levels);

                    ExcelAddressNumbers cursorAddress = ExcelHelper.SplitAddress("B12");
                    foreach (var levelData in squareDataLevel1.OrderBy(x => x.Level))
                    {
                        CreateLevelTable(cursorAddress, ws, rangeReview, levelData);
                        cursorAddress.FromColumn += 4;
                    }

                    titleAddress = ExcelHelper.SplitAddress("B21");
                    CreateTitleLine(titleAddress, ws, "Level 1: Linear Meters", levels);

                    cursorAddress = ExcelHelper.SplitAddress("B23");
                    foreach (var levelData in linearDataLevel1.OrderBy(x => x.Level))
                    {
                        CreateLevelTable(cursorAddress, ws, rangeReview, levelData);
                        cursorAddress.FromColumn += 4;
                    }
                    #endregion

                    #region Level 2

                    p.Workbook.Worksheets.Add("Level 2");
                    ws = p.Workbook.Worksheets[2];
                    ws.View.ShowGridLines = false;
                    ws.View.ZoomScale = 80;
                    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
                    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet


                    var dataLevel2 = from item in spaceRows.Where(x => x.LinearMeters)
                                     group item by new { item.Level1, item.Level2 } into g
                                     select new FloorplanData
                                     {
                                         ParentLevel = g.Key.Level1,
                                         Level = g.Key.Level2,
                                         Sales = g.Sum(x => x.Sales),
                                         GM = g.Sum(x => x.GM),
                                         SKUs = g.Count(),
                                         InventoryCost = g.Sum(x => x.InventoryCost),
                                         Space = g.Sum(x => x.Width) / 100,
                                         SalesPerc = linearTotals.Sales != 0 ? g.Sum(x => x.Sales) / linearTotals.Sales : 0,
                                         GMPerc = linearTotals.GM != 0 ? g.Sum(x => x.GM) / linearTotals.GM : 0,
                                         SKUsPerc = linearTotals.SKUs != 0 ? g.Count() / linearTotals.SKUs : 0,
                                         InventoryCostPerc = linearTotals.InventoryCost != 0 ? g.Sum(x => x.InventoryCost) / linearTotals.InventoryCost : 0,
                                         SpacePerc = linearTotals.Space != 0 ? (g.Sum(x => x.Width) / 100) / linearTotals.Space : 0,
                                     };

                    totalStoreAddress = ExcelHelper.SplitAddress("B2");
                    CreateTotalStoreTable(totalStoreAddress, ws, linearTotals);

                    CreateSemaphoreTable("F2", ws, rangeReview);

                    titleAddress = ExcelHelper.SplitAddress("B10");
                    CreateTitleLine(titleAddress, ws, "Level 2", levels);

                    cursorAddress = ExcelHelper.SplitAddress("B12");
                    int originalFromColumn = cursorAddress.FromColumn;
                    string prevLevel = dataLevel2.OrderBy(x => x.ParentLevel).Any() ? dataLevel2.OrderBy(x => x.ParentLevel).First().ParentLevel : "";
                    foreach (var levelData in dataLevel2.OrderBy(x => x.ParentLevel).ThenBy(x => x.Level))
                    {
                        if (prevLevel != levelData.ParentLevel)
                        {
                            cursorAddress.FromRow += 10;
                            prevLevel = levelData.ParentLevel;

                            titleAddress.FromRow += 10;
                            CreateTitleLine(titleAddress, ws, string.Empty, levels);

                            cursorAddress.FromColumn = originalFromColumn;
                        }

                        CreateLevelTable(cursorAddress, ws, rangeReview, levelData);
                        cursorAddress.FromColumn += 4;
                    }


                    #endregion

                    #region Level 3

                    p.Workbook.Worksheets.Add("Level 3");
                    ws = p.Workbook.Worksheets[3];
                    ws.View.ShowGridLines = false;
                    ws.View.ZoomScale = 80;
                    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
                    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet


                    var dataLevel3 = from item in spaceRows.Where(x => x.LinearMeters)
                                     group item by new { item.Level2, item.PlanogramName } into g
                                     select new FloorplanData
                                     {
                                         ParentLevel = g.Key.Level2,
                                         Level = g.Key.PlanogramName,
                                         Sales = g.Sum(x => x.Sales),
                                         GM = g.Sum(x => x.GM),
                                         SKUs = g.Count(),
                                         InventoryCost = g.Sum(x => x.InventoryCost),
                                         Space = g.Sum(x => x.Width) / 100,
                                         SalesPerc = linearTotals.Sales != 0 ? g.Sum(x => x.Sales) / linearTotals.Sales : 0,
                                         GMPerc = linearTotals.GM != 0 ? g.Sum(x => x.GM) / linearTotals.GM : 0,
                                         SKUsPerc = linearTotals.SKUs != 0 ? g.Count() / linearTotals.SKUs : 0,
                                         InventoryCostPerc = linearTotals.InventoryCost != 0 ? g.Sum(x => x.InventoryCost) / linearTotals.InventoryCost : 0,
                                         SpacePerc = linearTotals.Space != 0 ? (g.Sum(x => x.Width) / 100) / linearTotals.Space : 0,
                                     };

                    totalStoreAddress = ExcelHelper.SplitAddress("C2");
                    CreateTotalStoreTable(totalStoreAddress, ws, linearTotals);

                    CreateSemaphoreTable("G2", ws, rangeReview);

                    titleAddress = ExcelHelper.SplitAddress("C10");
                    CreateTitleLine(titleAddress, ws, "Level 3", levels);

                    cursorAddress = ExcelHelper.SplitAddress("C12");
                    originalFromColumn = cursorAddress.FromColumn;
                    prevLevel = dataLevel3.OrderBy(x => x.ParentLevel).Any() ? dataLevel3.OrderBy(x => x.ParentLevel).First().ParentLevel : "";
                    foreach (var levelData in dataLevel3.OrderBy(x => x.ParentLevel).ThenBy(x => x.Level))
                    {
                        if (prevLevel != levelData.ParentLevel)
                        {
                            cursorAddress.FromRow += 10;
                            prevLevel = levelData.ParentLevel;

                            titleAddress.FromRow += 10;
                            CreateTitleLine(titleAddress, ws, string.Empty, levels);

                            cursorAddress.FromColumn = originalFromColumn;
                        }

                        CreateLevelTable(cursorAddress, ws, rangeReview, levelData);
                        cursorAddress.FromColumn += 4;
                    }


                    #endregion

                    #region Level 4

                    p.Workbook.Worksheets.Add("Level 4");
                    ws = p.Workbook.Worksheets[4];
                    ws.View.ShowGridLines = false;
                    ws.View.ZoomScale = 80;
                    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
                    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

                    var dataLevel4 = from item in spaceRows.Where(x => x.LinearMeters && !string.IsNullOrWhiteSpace(x.Level4))
                                     group item by new { item.PlanogramName, item.Level4 } into g
                                     select new FloorplanData
                                     {
                                         ParentLevel = g.Key.PlanogramName,
                                         Level = g.Key.Level4,
                                         Sales = g.Sum(x => x.Sales),
                                         GM = g.Sum(x => x.GM),
                                         SKUs = g.Count(),
                                         InventoryCost = g.Sum(x => x.InventoryCost),
                                         Space = g.Sum(x => x.Width) / 100,
                                         SalesPerc = linearTotals.Sales != 0 ? g.Sum(x => x.Sales) / linearTotals.Sales : 0,
                                         GMPerc = linearTotals.GM != 0 ? g.Sum(x => x.GM) / linearTotals.GM : 0,
                                         SKUsPerc = linearTotals.SKUs != 0 ? g.Count() / linearTotals.SKUs : 0,
                                         InventoryCostPerc = linearTotals.InventoryCost != 0 ? g.Sum(x => x.InventoryCost) / linearTotals.InventoryCost : 0,
                                         SpacePerc = linearTotals.Space != 0 ? (g.Sum(x => x.Width) / 100) / linearTotals.Space : 0,
                                     };

                    totalStoreAddress = ExcelHelper.SplitAddress("C2");
                    CreateTotalStoreTable(totalStoreAddress, ws, linearTotals);

                    CreateSemaphoreTable("G2", ws, rangeReview);

                    titleAddress = ExcelHelper.SplitAddress("C10");
                    CreateTitleLine(titleAddress, ws, "Level 3", levels);

                    cursorAddress = ExcelHelper.SplitAddress("C12");
                    originalFromColumn = cursorAddress.FromColumn;
                    prevLevel = dataLevel4.OrderBy(x => x.ParentLevel).Any() ? dataLevel4.OrderBy(x => x.ParentLevel).First().ParentLevel : "";
                    foreach (var levelData in dataLevel4.OrderBy(x => x.ParentLevel).ThenBy(x => x.Level))
                    {
                        if (prevLevel != levelData.ParentLevel)
                        {
                            cursorAddress.FromRow += 10;
                            prevLevel = levelData.ParentLevel;

                            titleAddress.FromRow += 10;
                            CreateTitleLine(titleAddress, ws, string.Empty, levels);

                            cursorAddress.FromColumn = originalFromColumn;
                        }

                        CreateLevelTable(cursorAddress, ws, rangeReview, levelData);
                        cursorAddress.FromColumn += 4;
                    }

                    #endregion

                    #region % space to % MARGIN comparisonper category

                    p.Workbook.Worksheets.Add("Category Comparison");
                    ws = p.Workbook.Worksheets[5];
                    ws.View.ShowGridLines = false;
                    ws.View.ZoomScale = 80;
                    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
                    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

                    #region Data
                    var categoryRows = ReportsRepository.GetSalesCategoryComparison(rangeReview.Id, null, null, null);
                    List<SalesCategoryComparison> categoryItems = categoryRows.ConvertAll<SalesCategoryComparison>(x => new SalesCategoryComparison(x));

                    ws.Cells["F10"].Value = "Category";
                    ws.Cells["G10"].Value = "Sales/Linear mt.";
                    ws.Cells["H10"].Value = "GMROS";
                    ws.Cells["I10"].Value = "% Sales";
                    ws.Cells["J10"].Value = "% GIM";
                    ws.Cells["K10"].Value = "Space vs Sales";
                    ws.Cells["L10"].Value = "Space vs GIM";
                    ws.Cells["M10"].Value = "Remark";

                    decimal totalSpace = categoryItems.Where(x => !x.IsPriorYear).Sum(x => x.LinearSpace);


                    int row = 11;
                    int col = 6;
                    if (totalSpace != 0)
                    {
                        var totalStore = new SalesCategoryComparison
                        {
                            Category = "TOTAL STORE",
                            Sales = categoryItems.Where(x => !x.IsPriorYear).Sum(x => x.Sales),
                            GM = categoryItems.Where(x => !x.IsPriorYear).Sum(x => x.GM),
                            LinearSpace = categoryItems.Where(x => !x.IsPriorYear).Sum(x => x.LinearSpace)
                        };

                        foreach (var item in categoryItems.Where(x => !x.IsPriorYear))
                        {
                            decimal spaceShare = item.LinearSpace / totalSpace;
                            decimal SpaceVsSales = item.SalesShare != 0 ? spaceShare / item.SalesShare : 0;
                            decimal SpaceVsGIM = item.GMShare != 0 ? spaceShare / item.GMShare : 0;

                            if (item.GMROS < totalStore.GMROS)
                            {
                                ExcelHelper.SetStyles(ws.Cells[row, col, row, col + 6], ExcelBorderStyle.Thin, CategoryComparisonYellow);
                            }

                            ws.Cells[row, col++].Value = item.Category;
                            ws.Cells[row, col++].Value = item.SalesOverLinearMt;
                            ws.Cells[row, col++].Value = item.GMROS;
                            ws.Cells[row, col++].Value = item.SalesShare;
                            ws.Cells[row, col++].Value = item.GMShare;
                            ws.Cells[row, col++].Value = SpaceVsSales;
                            ws.Cells[row, col++].Value = SpaceVsGIM;

                            ExcelHelper.SetStyles(ws.Cells[row, col], ExcelBorderStyle.Thin, CategoryComparisonGray);
                            ws.Cells[row, col].Style.Font.Bold = true;
                            if (SpaceVsGIM < rangeReview.UnderSpaced)
                            {
                                ws.Cells[row, col].Value = "UNDER SPACED";
                                ws.Cells[row, col++].Style.Font.Color.SetColor(Yellow);
                            }
                            else if (SpaceVsGIM > rangeReview.OverSpaced)
                            {
                                ws.Cells[row, col].Value = "OVER SPACED";
                                ws.Cells[row, col++].Style.Font.Color.SetColor(Red);
                            }
                            else
                            {
                                ws.Cells[row, col].Value = "BALANCED";
                                ws.Cells[row, col++].Style.Font.Color.SetColor(Green);
                            }

                            col = 6;
                            row++;
                        }


                        ws.Cells[row, col++].Value = totalStore.Category;
                        ws.Cells[row, col++].Value = totalStore.SalesOverLinearMt;
                        ws.Cells[row, col++].Value = totalStore.GMROS;
                        ws.Cells[row, col++].Value = 1;
                        ws.Cells[row, col++].Value = 1;
                        ws.Cells[row, col++].Value = 1;
                        ws.Cells[row, col].Value = 1;
                    }
                    #endregion

                    #region Styles

                    //Column Widths
                    ws.Column(6).Width = 38;
                    ws.Column(7).Width = 14;
                    ws.Column(8).Width = 14;
                    ws.Column(13).Width = 16;

                    //Number Formats
                    ws.Cells[11, 7, row, 8].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                    ws.Cells[11, 9, row, 10].Style.Numberformat.Format = FormatPercentageOneDecimal;
                    ws.Cells[11, 11, row, 12].Style.Numberformat.Format = FormatTwoDecimals;

                    //Header Styles
                    ws.Cells[10, 6, 10, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    ws.Cells[10, 6, 10, 13].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    ws.Cells[10, 6, 10, 13].Style.WrapText = true;
                    ws.Cells[10, 6, 10, 13].Style.Font.Bold = true;
                    ws.Cells[10, 6, 10, 13].Style.Font.Color.SetColor(Color.White);
                    ExcelHelper.SetStyles(ws.Cells[10, 6, 10, 13], ExcelBorderStyle.Thin, BlueTotalTableHeader);

                    //Footer Styles
                    ws.Cells[row, 6, row, 13].Style.Font.Bold = true;
                    ExcelHelper.SetStyles(ws.Cells[row, 6, row, 13], ExcelBorderStyle.Thin, CategoryComparisonGray);

                    //Whole Table Styles
                    ExcelHelper.SetBorders(ws.Cells[11, 6, row, col], ExcelBorderStyle.Thin);

                    #endregion

                    #endregion
                }

                excelBytes = p.GetAsByteArray();

            }


            return excelBytes;

        }

        private static void CreateSemaphoreTable(string addressFrom, ExcelWorksheet ws, RangeReview range)
        {
            ExcelAddressNumbers addressNum = ExcelHelper.SplitAddress(addressFrom);

            int firstRow = addressNum.FromRow;
            int firstCol = addressNum.FromColumn;

            ExcelHelper.SetStyles(ws.Cells[firstRow, firstCol, firstRow, firstCol + 1], ExcelBorderStyle.Thin, GrayLine, Red);
            ExcelHelper.SetStyles(ws.Cells[firstRow + 1, firstCol, firstRow + 1, firstCol + 1], ExcelBorderStyle.Thin, GrayLine, Yellow);
            ExcelHelper.SetStyles(ws.Cells[firstRow + 2, firstCol, firstRow + 2, firstCol + 1], ExcelBorderStyle.Thin, GrayLine, Green);

            ws.Cells[firstRow, firstCol].Value = "Over Spaced";
            ws.Cells[firstRow, firstCol + 1].Value = range.OverSpaced.ToString("N1");
            ws.Cells[firstRow, firstCol + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[firstRow + 1, firstCol].Value = "Under Spaced";
            ws.Cells[firstRow + 1, firstCol + 1].Value = range.UnderSpaced.ToString("N1");
            ws.Cells[firstRow + 1, firstCol + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[firstRow + 2, firstCol].Value = "Balanced";
            ws.Cells[firstRow + 2, firstCol + 1].Value = string.Format("{0} to {1}", range.UnderSpaced.ToString("N1"), range.OverSpaced.ToString("N1"));
            ws.Cells[firstRow + 2, firstCol + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        }

        private static void CreateLevelTable(ExcelAddressNumbers startAddress, ExcelWorksheet ws, RangeReview range, FloorplanData data)
        {
            //First Column and First Row
            int fc = startAddress.FromColumn;
            int fr = startAddress.FromRow;

            if (fc != 0 && fr != 0)
            {
                ws.Cells[fr, fc].Value = data.Level;
                ws.Cells[fr + 1, fc].Value = "Sales";
                ws.Cells[fr + 2, fc].Value = "GI Margin";
                ws.Cells[fr + 3, fc].Value = "# SKUs";
                ws.Cells[fr + 4, fc].Value = "Inventory";
                ws.Cells[fr + 5, fc].Value = "Space";

                ws.Cells[fr + 1, fc + 1].Value = data.Sales / 1000;
                ws.Cells[fr + 2, fc + 1].Value = data.GM / 1000;
                ws.Cells[fr + 3, fc + 1].Value = data.SKUs;
                ws.Cells[fr + 4, fc + 1].Value = data.InventoryCost / 1000;
                ws.Cells[fr + 5, fc + 1].Value = data.Space;

                ws.Cells[fr + 1, fc + 2].Value = data.SalesPerc;
                ws.Cells[fr + 2, fc + 2].Value = data.GMPerc;
                ws.Cells[fr + 3, fc + 2].Value = data.SKUsPerc;
                ws.Cells[fr + 4, fc + 2].Value = data.InventoryCostPerc;
                ws.Cells[fr + 5, fc + 2].Value = data.SpacePerc;
            }

            decimal spaceVsGim = data.GMPerc != 0 ? data.SpacePerc / data.GMPerc : 0;

            ws.Cells[fr + 7, fc].Value = "Space vs. GIM";
            ws.Cells[fr + 7, fc + 2].Value = spaceVsGim;
            ws.Cells[fr + 7, fc + 2].Style.Numberformat.Format = "0.00";

            //STYLES AND FORMATS
            ws.Cells[fr + 1, fc + 1].Style.Numberformat.Format = FormatThousandsOneDecimal;
            ws.Cells[fr + 2, fc + 1].Style.Numberformat.Format = FormatThousandsOneDecimal;
            ws.Cells[fr + 4, fc + 1].Style.Numberformat.Format = FormatThousandsOneDecimal;
            ws.Cells[fr + 5, fc + 1].Style.Numberformat.Format = FormatMetersTwoDecimal;

            ws.Cells[fr + 1, fc + 2].Style.Numberformat.Format = FormatPercentageOneDecimal;
            ws.Cells[fr + 2, fc + 2].Style.Numberformat.Format = FormatPercentageOneDecimal;
            ws.Cells[fr + 3, fc + 2].Style.Numberformat.Format = FormatPercentageOneDecimal;
            ws.Cells[fr + 4, fc + 2].Style.Numberformat.Format = FormatPercentageOneDecimal;
            ws.Cells[fr + 5, fc + 2].Style.Numberformat.Format = FormatPercentageOneDecimal;




            if (spaceVsGim > range.OverSpaced)
            {
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr + 5, fc + 2], ExcelBorderStyle.None, Red);
                ws.Cells[fr, fc, fr + 5, fc + 2].Style.Font.Color.SetColor(Color.White);
            }
            else if (spaceVsGim < range.UnderSpaced)
            {
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr + 5, fc + 2], ExcelBorderStyle.None, Yellow);
            }
            else
            {
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr + 5, fc + 2], ExcelBorderStyle.None, Green);
                ws.Cells[fr, fc, fr + 5, fc + 2].Style.Font.Color.SetColor(Color.White);
            }

            ws.Cells[fr, fc, fr + 7, fc + 2].Style.Font.Bold = true;

            ws.Cells[fr, fc, fr, fc + 2].Style.Border.Top.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr, fc, fr, fc + 2].Style.Border.Top.Color.SetColor(GrayLine);

            ws.Cells[fr, fc, fr + 5, fc].Style.Border.Left.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr, fc, fr + 5, fc].Style.Border.Left.Color.SetColor(GrayLine);

            ws.Cells[fr + 5, fc, fr + 5, fc + 2].Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr + 5, fc, fr + 5, fc + 2].Style.Border.Bottom.Color.SetColor(GrayLine);

            ws.Cells[fr, fc + 2, fr + 5, fc + 2].Style.Border.Right.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr, fc + 2, fr + 5, fc + 2].Style.Border.Right.Color.SetColor(GrayLine);


            ws.Cells[fr + 7, fc, fr + 7, fc + 2].Style.Border.Top.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr + 7, fc, fr + 7, fc + 2].Style.Border.Top.Color.SetColor(GrayLine);

            ws.Cells[fr + 7, fc, fr + 7, fc + 2].Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr + 7, fc, fr + 7, fc + 2].Style.Border.Bottom.Color.SetColor(GrayLine);

            ws.Cells[fr + 7, fc].Style.Border.Left.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr + 7, fc].Style.Border.Left.Color.SetColor(GrayLine);

            ws.Cells[fr + 7, fc + 2].Style.Border.Right.Style = ExcelBorderStyle.Medium;
            ws.Cells[fr + 7, fc + 2].Style.Border.Right.Color.SetColor(GrayLine);

        }

        private static void CreateTitleLine(ExcelAddressNumbers cellsRange, ExcelWorksheet ws, string title, int countColumns)
        {

            cellsRange.ToRow = cellsRange.FromRow;
            cellsRange.ToColumn = cellsRange.FromColumn + (countColumns * 3) + (countColumns - 2);

            ws.Cells[cellsRange.FromRow, cellsRange.FromColumn, cellsRange.ToRow, cellsRange.ToColumn].Style.Border.Bottom.Style = ExcelBorderStyle.Thick;
            ws.Cells[cellsRange.FromRow, cellsRange.FromColumn, cellsRange.ToRow, cellsRange.ToColumn].Style.Border.Bottom.Color.SetColor(BlueLine);

            if (!string.IsNullOrWhiteSpace(title))
            {
                ws.Cells[cellsRange.FromRow, cellsRange.FromColumn].Value = title.ToUpper();
                ws.Cells[cellsRange.FromRow, cellsRange.FromColumn].Style.Font.Color.SetColor(BlueText);
                ws.Cells[cellsRange.FromRow, cellsRange.FromColumn].Style.Font.Bold = true;
            }

        }

        private static void CreateTotalStoreTable(ExcelAddressNumbers startAddress, ExcelWorksheet ws, FloorplanData linearData, FloorplanData squareData)
        {
            //First Column and First Row
            int fc = startAddress.FromColumn;
            int fr = startAddress.FromRow;

            if (fc != 0 && fr != 0)
            {
                ws.Cells[fr, fc].Value = "TOTAL STORE";
                ws.Cells[fr, fc + 1].Value = "Linear Data";
                ws.Cells[fr, fc + 2].Value = "Square Data";

                ws.Cells[fr + 1, fc].Value = "Sales";
                ws.Cells[fr + 2, fc].Value = "GI Margin";
                ws.Cells[fr + 3, fc].Value = "# SKUs";
                ws.Cells[fr + 4, fc].Value = "Inventory";
                ws.Cells[fr + 5, fc].Value = "Space";


                ws.Cells[fr + 1, fc + 1].Value = linearData.Sales / 1000;
                ws.Cells[fr + 2, fc + 1].Value = linearData.GM / 1000;
                ws.Cells[fr + 3, fc + 1].Value = linearData.SKUs;
                ws.Cells[fr + 4, fc + 1].Value = linearData.InventoryCost / 1000;
                ws.Cells[fr + 5, fc + 1].Value = linearData.Space;

                ws.Cells[fr + 1, fc + 2].Value = squareData.Sales / 1000;
                ws.Cells[fr + 2, fc + 2].Value = squareData.GM / 1000;
                ws.Cells[fr + 3, fc + 2].Value = squareData.SKUs;
                ws.Cells[fr + 4, fc + 2].Value = squareData.InventoryCost / 1000;
                ws.Cells[fr + 5, fc + 2].Value = squareData.Space;


                //STYLES AND FORMATS
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr + 5, fc + 2], ExcelBorderStyle.Thin, GrayLine, Color.White);
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr, fc + 2], ExcelBorderStyle.Thin, GrayLine, BlueTotalTableHeader);
                ws.Cells[fr, fc, fr, fc + 2].Style.Font.Color.SetColor(Color.White);
                ws.Cells[fr, fc, fr, fc + 2].Style.Font.Bold = true;

                ws.Cells[fr + 1, fc + 1, fr + 5, fc + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.General;

                ws.Cells[fr + 1, fc + 1].Style.Numberformat.Format = FormatThousandsTwoDecimals;
                ws.Cells[fr + 2, fc + 1].Style.Numberformat.Format = FormatThousandsTwoDecimals;
                ws.Cells[fr + 4, fc + 1].Style.Numberformat.Format = FormatThousandsTwoDecimals;

                ws.Cells[fr + 1, fc + 2].Style.Numberformat.Format = FormatThousandsTwoDecimals;
                ws.Cells[fr + 2, fc + 2].Style.Numberformat.Format = FormatThousandsTwoDecimals;
                ws.Cells[fr + 4, fc + 2].Style.Numberformat.Format = FormatThousandsTwoDecimals;
            }
        }

        private static void CreateTotalStoreTable(ExcelAddressNumbers startAddress, ExcelWorksheet ws, FloorplanData linearData)
        {
            //First Column and First Row
            int fc = startAddress.FromColumn;
            int fr = startAddress.FromRow;

            if (fc != 0 && fr != 0)
            {
                ws.Cells[fr, fc].Value = "TOTAL STORE";
                ws.Cells[fr, fc + 1].Value = "Linear Data";

                ws.Cells[fr + 1, fc].Value = "Sales";
                ws.Cells[fr + 2, fc].Value = "GI Margin";
                ws.Cells[fr + 3, fc].Value = "# SKUs";
                ws.Cells[fr + 4, fc].Value = "Inventory";
                ws.Cells[fr + 5, fc].Value = "Space";


                ws.Cells[fr + 1, fc + 1].Value = linearData.Sales / 1000;
                ws.Cells[fr + 2, fc + 1].Value = linearData.GM / 1000;
                ws.Cells[fr + 3, fc + 1].Value = linearData.SKUs;
                ws.Cells[fr + 4, fc + 1].Value = linearData.InventoryCost / 1000;
                ws.Cells[fr + 5, fc + 1].Value = linearData.Space;


                //STYLES AND FORMATS
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr + 5, fc + 1], ExcelBorderStyle.Thin, GrayLine, Color.White);
                ExcelHelper.SetStyles(ws.Cells[fr, fc, fr, fc + 1], ExcelBorderStyle.Thin, GrayLine, BlueTotalTableHeader);
                ws.Cells[fr, fc, fr, fc + 1].Style.Font.Color.SetColor(Color.White);
                ws.Cells[fr, fc, fr, fc + 1].Style.Font.Bold = true;

                ws.Cells[fr + 1, fc + 1, fr + 5, fc + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.General;

                ws.Cells[fr + 1, fc + 1].Style.Numberformat.Format = FormatThousandsTwoDecimals;
                ws.Cells[fr + 2, fc + 1].Style.Numberformat.Format = FormatThousandsTwoDecimals;
                ws.Cells[fr + 4, fc + 1].Style.Numberformat.Format = FormatThousandsTwoDecimals;

            }
        }

    }

    class FloorplanData
    {
        public string ParentLevel { get; set; }
        public string Level { get; set; }
        public decimal Sales { get; set; }
        public decimal GM { get; set; }
        public int SKUs { get; set; }
        public decimal InventoryCost { get; set; }
        public decimal Space { get; set; }

        public decimal SalesPerc { get; set; }
        public decimal GMPerc { get; set; }
        public int SKUsPerc { get; set; }
        public decimal InventoryCostPerc { get; set; }
        public decimal SpacePerc { get; set; }
    }
}
